﻿using Os.Infra;
using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Data;

namespace Os.Domain
{
    public class Lote
    {
        #region Atributos

        private int _idLote;
        private int _competencia;
        private TimeSpan _horasContrato;
        private TimeSpan _horasExcedentes;
        private TimeSpan _horasUtilizadas;
        private TimeSpan _saldoHoras;
        private Cliente _cliente;
        private float _total;

        private bool _modified;
        private bool _persisted;

        private const string SPINSERT = "SP_LoteINSERT";
        private const string SPUPDATE = "SP_LoteUPDATE";
        private const string SPDELETE = "SP_LoteDELETE";
        private const string SPSELECT = "SP_LoteSELECT";
        private const string SPSELECTID = "SP_LoteSELECTID";

        #endregion Atributos

        #region propriedades

        public int IdLote
        {
            get
            {
                return this._idLote;
            }
            set
            {
                this._idLote = value;
                this._modified = true;
            }
        }
        public int Competencia
        {
            get
            {
                return this._competencia;
            }
        }


        public TimeSpan HorasContrato
        {
            get
            {
                return this._horasContrato;
            }
            set
            {
                this._horasContrato = value;
                this._modified = true;
            }
        }
        public TimeSpan HorasExcedentes
        {
            get { return this._horasExcedentes; }
            set 
            {
                this._horasExcedentes = value;
                this._modified = true;
            }
        }
        public TimeSpan HorasUtilizadas
        {
            get { return this._horasUtilizadas; }
            set
            {
                this._horasUtilizadas = value;
                this._modified = true;
            }
        }
        public TimeSpan SaldoHoras
        {
            get { return this._saldoHoras; }
            set
            {
                this.SaldoHoras = value;
                this._modified = true;
            }
        }
        public float Total
        {
            get { return this._total; }
            set
            {
                this._total = value;
                this._modified = true;
            }
        }
        public Cliente Cliente
        {
            get { return this._cliente; }
            set 
            {
                this._cliente = value;
                this._modified = true;
            }
        }

        #endregion Propriedades

        #region Métodos

        /// <summary>
        /// 
        /// </summary>
        public Lote()
        {
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="idLote"></param>
        public Lote(int idLote)
        {
            this._idLote = idLote;
            this._persisted = true;
        }
        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        private List<MySqlParameter> ObterParametros()
        {
            return new List<MySqlParameter>
			{
				new MySqlParameter("?parIdLote", MySqlDbType.Int32, 11), 
				new MySqlParameter("?parCompetencia", MySqlDbType.Int32, 11), 
				new MySqlParameter("?parHorasContrato", MySqlDbType.Time),
                new MySqlParameter("?parHorasExcedentes", MySqlDbType.Time), 
				new MySqlParameter("?parHorasUtilizadas", MySqlDbType.Time), 
				new MySqlParameter("?parSaldoHoras", MySqlDbType.Time),
                new MySqlParameter("?parTotal", MySqlDbType.Float),
                new MySqlParameter("?parCliente", MySqlDbType.Int32,11)
			};
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="parms"></param>
        private void PreencherParametros(List<MySqlParameter> parms)
        {
            parms[0].Value = this._idLote;
            parms[1].Value = this._competencia;
            parms[2].Value = this._horasContrato;
            parms[3].Value = this._horasExcedentes;
            parms[4].Value = this._horasUtilizadas;
            parms[5].Value = this._saldoHoras;
            parms[6].Value = this._total;

            if (this._cliente == null)
            {
                parms[7].Value = DBNull.Value;
            }
            else
            {
                parms[7].Value = this._cliente.IdCliente;
            }

            if (!this._persisted)
            {
                parms[0].Direction = ParameterDirection.Output;
            }
            else
            {
                parms[0].Direction = ParameterDirection.Input;
            }
        }
        /// <summary>
        /// 
        /// </summary>
        private void Inserir()
        {
            List<MySqlParameter> parameters = this.ObterParametros();
            this.PreencherParametros(parameters);
            using (MySqlConnection mySqlConnection = new MySqlConnection(MysqlData.CONN_STRING))
            {
                mySqlConnection.Open();
                try
                {
                    MySqlCommand mySqlCommand = MysqlData.ExecutarComando(CommandType.StoredProcedure, "SP_LoteINSERT", parameters);
                    this._idLote = Convert.ToInt32(mySqlCommand.Parameters["?parIdLote"].Value);
                    mySqlCommand.Parameters.Clear();
                    this._persisted = true;
                    this._modified = false;
                }
                catch (Exception ex)
                {
                    throw ex;
                }
                finally
                {
                    mySqlConnection.Close();
                }
            }
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="trans"></param>
        private void Inserir(MySqlTransaction trans)
        {
            List<MySqlParameter> parameters = this.ObterParametros();
            this.PreencherParametros(parameters);
            MySqlCommand mySqlCommand = MysqlData.ExecutarComando(trans, CommandType.StoredProcedure, "SP_LoteINSERT", parameters);
            this._idLote = Convert.ToInt32(mySqlCommand.Parameters["?id_Lote"].Value);
            mySqlCommand.Parameters.Clear();
            this._persisted = true;
            this._modified = false;
        }
        /// <summary>
        /// 
        /// </summary>
        private void Atualizar()
        {
            if (this._modified)
            {
                List<MySqlParameter> parameters = this.ObterParametros();
                this.PreencherParametros(parameters);
                MysqlData.ExecutarComando(CommandType.StoredProcedure, "SP_LoteUPDATE", parameters);
                this._modified = false;
            }
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="trans"></param>
        private void Atualizar(MySqlTransaction trans)
        {
            if (this._modified)
            {
                List<MySqlParameter> parameters = this.ObterParametros();
                this.PreencherParametros(parameters);
                MysqlData.ExecutarComando(trans, CommandType.StoredProcedure, "SP_LoteUPDATE", parameters);
                this._modified = false;
            }
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="idCidade"></param>
        public static void Remover(int idCidade)
        {
            List<MySqlParameter> parms = new List<MySqlParameter>
			{
				new MySqlParameter("?idCidade", MySqlDbType.Int32, 11)
			};
            parms[0].Value = idCidade;
            MysqlData.ExecutarComando(CommandType.StoredProcedure, "SP_LoteDELETE", parms);
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="trans"></param>
        /// <param name="idCidade"></param>
        public static void Remover(MySqlTransaction trans, int idCidade)
        {
            List<MySqlParameter> parms = new List<MySqlParameter>
			{
				new MySqlParameter("?idCidade", MySqlDbType.Int32, 11)
			};
            parms[0].Value = idCidade;
            MySqlCommand mySqlCommand = MysqlData.ExecutarComando(trans, CommandType.StoredProcedure, "SP_LoteDELETE", parms);
            mySqlCommand.Parameters.Clear();
        }
        /// <summary>
        /// 
        /// </summary>
        public void Salvar()
        {
            if (!this._persisted)
            {
                this.Inserir();
            }
            else
            {
                this.Atualizar();
            }
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="trans"></param>
        public void Salvar(MySqlTransaction trans)
        {
            if (!this._persisted)
            {
                this.Inserir(trans);
            }
            else
            {
                this.Atualizar(trans);
            }
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="dr"></param>
        /// <param name="objLote"></param>
        private static void PreencherInstancia(IDataReader dr, Lote objLote)
        {
            try
            {
                if (dr["idLote"] != DBNull.Value)
                {
                    objLote._idLote = Convert.ToInt32(dr["idLote"]);
                }
                if (dr["competencia"] != DBNull.Value)
                {
                    objLote._competencia = Convert.ToInt32(dr["competencia"]);
                }
                if (dr["horasContrato"] != DBNull.Value)
                {
                    objLote._horasContrato = (TimeSpan)dr["horasContrato"];
                }
                if (dr["horasExcedentes"] != DBNull.Value)
                {
                    objLote._horasExcedentes = (TimeSpan)dr["horasExcedentes"];
                }
                if (dr["horasUtilizadas"] != DBNull.Value)
                {
                    objLote._horasUtilizadas = (TimeSpan)dr["horasUtilizadas"];
                }
                if (dr["saldoHoras"] != DBNull.Value)
                {
                    objLote._saldoHoras = (TimeSpan)dr["saldoHoras"];
                }
                if (dr["total"] != DBNull.Value)
                {
                    objLote._total = (float)dr["total"];
                }
                if (dr["clientes_idCliente"] != DBNull.Value)
                {
                    objLote._cliente = Cliente.CarregarObjeto(Convert.ToInt32(dr["clientes_idCliente"]));
                }

                objLote._persisted = true;
                objLote._modified = false;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public void InicializaCompetencia(DateTime data)
        {
            string competencia = data.Month < 10 ? data.Year.ToString() + "0" + data.Month.ToString() : data.Year.ToString() + data.Month.ToString();
            this._competencia = Convert.ToInt32(competencia);
            this._modified = true;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="idLote"></param>
        /// <returns></returns>
        public static Lote CarregarObjeto(int idLote)
        {
            Lote Lote = null;
            using (IDataReader dr = Lote.CarregarDados(idLote,"Lote",null))
            {
                if (dr.Read())
                {
                    Lote = new Lote();
                    Lote.PreencherInstancia(dr, Lote);
                }
                dr.Close();
                dr.Dispose();
            }
            return Lote;
        }
        /// <summary>
        /// Carregar Objeto pela Competência
        /// </summary>
        /// <param name="competencia"></param>
        /// <returns></returns>
        public static Lote CarregarPorCompetencia(DateTime competencia,Cliente objCliente)
        {
            Lote lote = new Lote();

            lote.InicializaCompetencia(competencia);
            using (IDataReader dr = Lote.CarregarDados(lote.Competencia,"Competencia",objCliente))
            {
                if (dr.Read())
                {
                    Lote.PreencherInstancia(dr, lote);
                }
                else
                {
                    lote.Salvar();
                }
                dr.Close();
                dr.Dispose();
            }
            return lote;
        }
        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        public static List<Lote> CarregarTodos()
        {
            List<Lote> lotes = new List<Lote>();
            using (IDataReader dr = Lote.CarregarDados())
            {
                while (dr.Read())
                {
                    Lote objLote = new Lote();
                    Lote.PreencherInstancia(dr, objLote);
                    lotes.Add(objLote);
                }
                dr.Close();
                dr.Dispose();
            }
            return lotes;
        }

        /// <summary>
        /// 
        /// </summary>
        public void CompletarObjeto()
        {
            using (IDataReader dr = Lote.CarregarDados(this._idLote,"Lote",null))
            {
                if (dr.Read())
                {
                    Lote.PreencherInstancia(dr, this);
                }
                dr.Close();
                dr.Dispose();
            }
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="parIdLote"></param>
        /// <returns></returns>
        private static IDataReader CarregarDados(int parChave,string campo,Cliente objCliente)
        {
            List<MySqlParameter> parms = new List<MySqlParameter>();

            string StoredProcedure = string.Empty;
            if (campo == "Lote")
            {
                parms.Add(new MySqlParameter("?parIdLote", MySqlDbType.Int32, 11));
                StoredProcedure = "SP_LoteSELECTID";
                parms[0].Value = parChave;
            }
            else if (campo == "Competencia")
            {
                parms.Add(new MySqlParameter("?parCompetencia", MySqlDbType.Int32, 11));
                parms.Add(new MySqlParameter("?parCliente", MySqlDbType.Int32, 11));

                StoredProcedure = "SP_LoteSELECTCompetenciaAndCliente";
                
                parms[0].Value = parChave;
                parms[1].Value = objCliente.IdCliente;
            }
            
            return MysqlData.ExecutarLeitura(CommandType.StoredProcedure, StoredProcedure, parms);
        }
        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        private static IDataReader CarregarDados()
        {
            return MysqlData.ExecutarLeitura(CommandType.StoredProcedure, "SP_LoteSELECT", null);
        }

        #endregion Métodos
    }
}
